﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MyQQ
{
    /// <summary>
    /// 数据库操作
    /// </summary>
    public class DataOperator
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private static readonly string connString = @"Data Source=LSP;Database=db_MyQQ;User ID=sa;Pwd=888888;";
        /// <summary>
        /// 数据库连接对象
        /// </summary>
        public static SqlConnection connection = new SqlConnection(connString);

        /// <summary>
        /// 返回第一行第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecSQL(string sql)
        {
            //指定要执行的sql语句
            SqlCommand command = new SqlCommand(sql, connection);
            //如果数据库处于关闭状态
            if (connection.State == ConnectionState.Closed)
                connection.Open();
            //执行查询并且在结果集中取第一行的第一列
            int num = Convert.ToInt32(command.ExecuteScalar());
            //关闭数据库
            connection.Close();
            return num;
        }

        /// <summary>
        /// 返回受影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecSQLResult(string sql)
        {
            SqlCommand command = new SqlCommand(sql, connection);
            if (connection.State == ConnectionState.Closed)
                connection.Open();
            //执行sql语句并且返回受影响的行数
            int result = command.ExecuteNonQuery();
            connection.Close();
            return result;
        }

        /// <summary>
        /// 自动登陆，返回数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql)
        {
            //指定要执行的sql语句
            SqlDataAdapter sqlData = new SqlDataAdapter(sql, connection);
            DataSet data = new DataSet();
            //填充数据集
            sqlData.Fill(data);
            //返回数据集
            return data;
        }

        /// <summary>
        /// 用户和好友相关信息
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public SqlDataReader GetDataRender(string sql)
        {
            SqlCommand command = new SqlCommand(sql,connection);
            if (connection.State == ConnectionState.Open)
                connection.Close();
            connection.Open();
            //返回一个对象
            SqlDataReader dataReader = command.ExecuteReader();
            return dataReader;
        }
    }
}
